﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

namespace DataDAL
{
    public class TrainDAL
    {
        /// <summary>
        /// 事务，批量保存火车过衡数据列表，返回插入的数据行数
        /// </summary>
        /// <param name="list"></param>
        /// <returns></returns>
        public int AddList(List<CoalByTrain> list)
        {
            int count = 0;
            OracleConnection connection = null;
            OracleTransaction tran = null;
            string date = DateTime.Now.ToString("yyyy-MM-dd");
            sbyte piNo = GetTrainPiNo(date);//获取批号
            try
            {
                string conn = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
                connection = new OracleConnection(conn);
                connection.Open();              
                OracleCommand cmd = connection.CreateCommand();
                tran = connection.BeginTransaction(IsolationLevel.Serializable); 
                cmd.Transaction = tran;
                foreach (var item in list)
                {
                    string sql = "insert into COALBYTRAIN(coalbytrainid,measuretime,vehicleno,billweight,GROSSWEIGHT,TARE,TRAINTYPE,SEQUENCENO,SPEED,BATCHNUMBER) values(COALBYTRAIN_SEQ.NEXTVAL,to_date(:GuoHengTime,'yyyy-mm-dd hh24:mi:ss'),:vehicleno,:billweight,:GROSSWEIGHT,:TARE,:TRAINTYPE,:SEQUENCENO,:SPEED,:BATCHNUMBER)";
                    cmd.Parameters.Add(new OracleParameter(":GuoHengTime", item.GuoHengTime));
                    cmd.Parameters.Add(new OracleParameter(":vehicleno", item.TrainNo));
                    cmd.Parameters.Add(new OracleParameter(":billweight", item.BiaoWeight));
                    cmd.Parameters.Add(new OracleParameter(":GROSSWEIGHT", item.GrossWeight));
                    cmd.Parameters.Add(new OracleParameter(":TARE", item.Tare));
                    cmd.Parameters.Add(new OracleParameter(":TRAINTYPE", item.TrainType));
                    cmd.Parameters.Add(new OracleParameter(":SEQUENCENO", item.SeqNo));
                    cmd.Parameters.Add(new OracleParameter(":SPEED", item.Speed));
                    cmd.Parameters.Add(new OracleParameter(":BATCHNUMBER", OracleDbType.Varchar2, piNo, ParameterDirection.Input));//批号
                    cmd.CommandText = sql;
                    int ef = cmd.ExecuteNonQuery();
                    count += ef;
                    //LogManager.AddLog("数据库影响行数" + ef, "Log");
                    cmd.Parameters.Clear();                  
                }
                tran.Commit();
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                count = 0;
                if (tran != null)
                {
                    tran.Rollback();
                }
                string msg = ex.Message;
                if (ex.InnerException != null)
                {
                    msg = ex.InnerException.Message;
                }
                LogManager.AddLog("数据库执行异常1：" + msg, "Log");
            }
            finally
            {
                if (tran != null)
                {
                    tran.Dispose();
                }
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return count;
        }

        /// <summary>
        /// 火车当前的批号，以备授予当前要插入的数据
        /// </summary>
        /// <param name="date"></param>
        /// <returns></returns>
        public sbyte GetTrainPiNo(string date)
        {
            sbyte currentPc = 0;
            OracleConnection connection = null;
            try
            {
                string conn = System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
                connection = new OracleConnection(conn);
                connection.Open();
                //查询当天最大的批号
                string sql_pc = "select NVL(max(t.batchnumber),0) from COALBYTRAIN t where to_char(t.measuretime,'yyyy-mm-dd')='" + date + "'";
                OracleCommand cmd1 = new OracleCommand(sql_pc, connection);
                object obj_pc = cmd1.ExecuteScalar();
                if (obj_pc != DBNull.Value && obj_pc != null)
                {
                    currentPc = Convert.ToSByte(obj_pc);
                }
                currentPc = Convert.ToSByte(currentPc + 1);//计算出当前的批号
                cmd1.Dispose();
            }
            catch (Exception ex)
            {
                string msg = ex.Message;
                if (ex.InnerException != null)
                {
                    msg = ex.InnerException.Message;
                }
                LogManager.AddLog("数据库执行异常2：" + msg, "Log");
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return currentPc;
        }
    }
}
